﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;

namespace 列车时刻查询系统
{
    public partial class Form1 : Form
    {
        SqlConnection conn = null;//定义数据库引用
        public Form1()
        {
            InitializeComponent();
        }
        public Form1 (string username) : this()
        {
            this.Text = username + "欢迎你！";
            linkLabel1.Text = username;
        }

        private void textBox1_TextChanged(object sender, EventArgs e)
        {

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: 这行代码将数据加载到表“traintimeDataSet1.liechebiao”中。您可以根据需要移动或删除它。
            this.liechebiaoTableAdapter1.Fill(this.traintimeDataSet1.liechebiao);
                        
            string sql = "server=.;database=traintime;integrated security=true;";
            conn = new SqlConnection(sql);
            conn.Open();
            if (conn.State == ConnectionState.Open)
            {
                this.Text = "列车时刻查询系统(已连接!)";
            }
            
        }

        private void button1_Click(object sender, EventArgs e)//查询按钮
        {
            string s = dateTimePicker1.Value.ToString("yyyyMMdd");
            if (radioButton1.Checked != true)
            {
                if (textBox1.Text == "")
                    MessageBox.Show("出发地或目的地不能为空！");
                else
                {
                    this.liechebiaoBindingSource.Filter = "chufadi ='" + textBox1.Text + "'";
                    //this.liechebiaoBindingSource.Filter = "mudidi='" + textBox2.Text + "'";
                }
            }
            if(radioButton1.Checked==true)
                this.liechebiaoBindingSource.Filter = "mudidi='" + dateTimePicker1.Value.ToString("yyyyMMdd") + "'";

        }

        private void pictureBox1_Click(object sender, EventArgs e)
        {
            string temp=textBox1.Text;
            textBox1.Text = textBox2.Text;
            textBox2.Text = temp;
        }

 
     
        private void button2_Click(object sender, EventArgs e)
        {
            login f1 = new login();
            f1.Show();
            //this.Hide();
        }

          private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            login log = new login();
            this.Hide();
            log.Show();
        }

        private void 数据库更新ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (linkLabel1.Text == "未登录")
            {
                MessageBox.Show("请先登录！");
                return;
            }
            else if (linkLabel1.Text != "admin")
            {
                MessageBox.Show("你不是管理员，无法执行该命令！");
                return;
            }
            else 
            {
                guanliyuan gly = new guanliyuan();
                this.Hide();
                gly.Show();
            }


        }
     
        private void button2_Click_1(object sender, EventArgs e)
        {
            //this.liechebiaoTableAdapter1.ClearBeforeFill;
            this.liechebiaoTableAdapter1.Fill(this.traintimeDataSet1.liechebiao);
           // liechebiaoTableAdapter1.GetData();
            //Form1 f2 = new Form1(this.Text);
            //f2.Show();
            //this.Hide();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (linkLabel1.Text == "admin")
            {
                this.liechebiaoTableAdapter1.Update(this.traintimeDataSet1.liechebiao);
                MessageBox.Show("修改成功！");
            }
            else
            {
                MessageBox.Show("非管理员无法执行此操作");
                return;
             }
        }

        private void 导出数据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            saveFileDialog1.ShowDialog();
        }

        private void saveFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
            string filename = this.saveFileDialog1.FileName;
            FileStream fs = File.Open(filename,FileMode.OpenOrCreate);
            StreamWriter sw = new StreamWriter(fs,Encoding.Default);
            SqlConnection conn = new SqlConnection("server=.;database=traintime;integrated security=true;");
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "select * from liechebiao";
            SqlDataReader reader = cmd.ExecuteReader();
            int i = 0;
            while(reader.Read())
            {
                string s=reader[0].ToString()+"\t"+
                    reader[1].ToString()+"\t"+
                    reader[2].ToString()+"\t"+
                    reader[3].ToString();
                sw.WriteLine(s);
                i++;
            }
            reader.Close();
                conn.Close();
                sw.Close();
                fs.Close();

                MessageBox.Show("已保存到文件" + filename + "，总共" + i + "条记录");

        }

        private void 导入数据ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            openFileDialog1.OpenFile();
            
        }

        private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
            
                string filename = this.openFileDialog1.FileName;
                FileStream fs = File.Open(filename, FileMode.Open);
                StreamReader sr = new StreamReader(fs, Encoding.Default);

                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = "server=.;database=traintime;integrated security=true;";
                conn.Open();

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "insert into liechebiao values(?,?,?,?)";
                cmd.Parameters.Add("@checi", SqlDbType.Char);
                cmd.Parameters.Add("@chufadi",SqlDbType.Char);
                cmd.Parameters.Add("@mudidi", SqlDbType.Char);
                cmd.Parameters.Add("@shijian", SqlDbType.Char);

                SqlCommand cmd2 = new SqlCommand();
                cmd2.Connection = conn;
                cmd2.CommandText = "select count(*) from liechebiao where checi=?";
                cmd2.Parameters.Add("@checi",SqlDbType.Char );

                int count = 0;
                while (!sr.EndOfStream)
                {
                    string s = sr.ReadLine();
                    MessageBox.Show(s);
                    string[] s2 = s.Split('\t');

                    cmd2.Parameters["@checi"].Value = s2[0];
                    int n = (int)cmd2.ExecuteScalar();
                    MessageBox.Show(n.ToString());
                    if (n == 0)
                    {
                        cmd.Parameters[0].Value = s2[0];
                        cmd.Parameters[1].Value = s2[1];
                        cmd.Parameters[2].Value = s2[2];
                        cmd.Parameters[3].Value = s2[3];
                         int i = cmd.ExecuteNonQuery();
                        MessageBox.Show(i.ToString());
                        count++;
                    }
                }

                MessageBox.Show("已导入" + count + "条记录！");
                sr.Close();
                fs.Close();
                conn.Close();
            }

        private void 帮助ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            MessageBox.Show("铁路时刻查询系统v1.0\n用户："+this.linkLabel1.Text);
        }

        private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
        {

        }

     
        }

}
